package com.wy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wy.dto.GoodsInfoDto;
import com.wy.entity.GoodsInfo;
import com.wy.entity.GoodsOrder;
import com.wy.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author 王渝
 */
@Mapper
public interface GoodsInfoMapper extends BaseMapper<GoodsInfo> {

    /*@Select("SELECT goods_id,goods_name, deposit,starting_time,end_time,price_markup,goods_info.`status`,type_name,user_name,starting_price,phone,email,break_count,image1,image2,image3,image4,image5,image6\n" +
            "FROM goods_info,goods_type,user_info,goods_image" +
            " WHERE goods_info.type_id = goods_type.type_id and user_info.user_id = goods_info.user_id and goods_image.image_id = goods_info.image_id and goods_info.status = #{status}")
    List<GoodsInfoDto> queryAllByStatus(String status);*/


    /**
     * 根据商品状态查询所以商品
     * @param status 商品状态
     * @return
     */
    @Select("SELECT goods_info.*,type_name from goods_info ,goods_type WHERE goods_info.type_id = goods_type.type_id and status = #{status} ")
    List<GoodsInfo> queryAllByStatus(String status);

    /**
     * 根据用具id 和商品状态查询所有商品
     * @param status 商品状态
     * @param userId 用户id
     * @return
     */
    @Select("SELECT goods_info.*,type_name " +
            "from goods_info ,goods_type " +
            "WHERE goods_info.type_id = goods_type.type_id and status = #{status} and user_id = #{userId} ")
    List<GoodsInfo> queryAllByStatusAndUserId(String status,Long userId);

    /**
     * 根据商品id查询商品详细信息
     * @param goodsId 商品id
     * @return
     */
    @Select("SELECT user_name,phone,email,break_count,goods_name,deposit,description,starting_price,type_name,starting_time,end_time,price_markup,goods_id,user_info.user_id " +
            "FROM user_info,goods_info,goods_type " +
            "WHERE user_info.user_id = goods_info.user_id and goods_info.type_id = goods_type.type_id and goods_id = #{goodId}")
    GoodsInfoDto queryDetailById(Long goodsId);

    /**
     * 根据分类信息查询商品
     * @param type 类型名称
     * @return
     */
    @Select("<script>" +
            "select  goods_name,starting_price,starting_time,end_time,image,goods_info.goods_id " +
            "FROM goods_info ,goods_image,goods_type " +
            "WHERE goods_info.goods_id = goods_image.goods_id  " +
            "and goods_info.type_id = goods_type.type_id " +
            "and goods_info.`status` = \"2\"" +
            " <if test='type!=null and type !=\"\" '>"+
            "and type_name = #{type}" +
            "</if>" +
            "GROUP BY goods_name "+
            "</script>"
            )
    List<GoodsInfo> queryAllByType(String type);

    /**
     * 将无人竞拍的过期商品设置为 流拍
     * @return
     */
    @Update(
        "UPDATE goods_info set `status` = 5 WHERE goods_info.end_time < now() and goods_info.sold = 1;\n"
    )
    int updateStatusSold1();

    /**
     * 将有人参与过的过期商品设置为  竞拍成功
     * @return
     */
    @Update(
            "UPDATE goods_info,goods_auction set goods_info.`status` = 4 , goods_auction.bidding = 2 " +
                    "WHERE goods_info.end_time < now() and goods_info.sold = 2\n" +
                    "AND goods_info.goods_id = goods_auction.goods_id "
    )
    int updateStatusSold2();

    /**
     * 查询拍卖的过期商品 将信息封装为订单
     * @return
     */
    @Select(
           "SELECT goods_info.user_id seller_user_id ,goods_info.current_user_id buyer_user_id ,bid_amount transaction_amount,goods_info.goods_id \n" +
                   "from goods_auction,goods_info\n" +
                   " WHERE goods_auction.ordering = 1\n" +
                   "and goods_info.goods_id = goods_auction.goods_id \n" +
                   "and goods_info.`status` = 4\n" +
                   "and goods_info.current_user_id = goods_auction.user_id")
    List<GoodsOrder> queryInfo();

    /**
     * 根据商品id查询用户邮箱
     * @param goodsId 商品id
     * @return
     */
    @Select("SELECT email " +
            "FROM user_info , goods_info " +
            "WHERE user_info.user_id =  goods_info.user_id " +
            "and goods_id = #{goodsId}")
    String  queryUserInfoByGoodsId(Long goodsId);



    @Select("SELECT bid_amount \n" +
            "from goods_auction,goods_info \n" +
            "WHERE goods_auction.user_id = goods_info.current_user_id \n" +
            "and goods_info.goods_id = goods_auction.goods_id \n" +
            "and goods_info.goods_id = #{goodsID}")
    /**
     * 根据商品id查询商品的最高出价
     * @param goodsId 商品id
     * @return
     */
    BigDecimal queryMaxPriceByGoodsId(Long goodsId);

    /**
     * 修改最高出价用户id
     * @param userId 用户id
     * @param goodsId 商品id
     * @return
     */
    @Update("update goods_info " +
            "set sold = 2,current_user_id = #{userId} WHERE goods_id = #{goodsId}")
    int updateCurrentUserId(Long userId,Long goodsId);


    /**
     * 根据用户id信息查询商品 （在售）
     * @param userId 用户id
     * @return
     */
    @Select("select  goods_name,starting_price,starting_time,end_time,image,goods_info.goods_id \n" +
            "FROM goods_info ,goods_image\n" +
            "WHERE goods_info.goods_id = goods_image.goods_id  \n" +
            "and goods_info.end_time > now()\n" +
            "and goods_info.`status` = \"2\"\n" +
            "AND user_id = #{userId}\n" +
            "GROUP BY goods_name ")
    List<GoodsInfo> queryAllByUserId(Long userId);

    /**
     * 根据用户id信息查询商品 （已结束）
     * @param userId 用户id
     * @return
     */
    @Select("select  goods_name,starting_price,starting_time,end_time,image,goods_info.goods_id \n" +
            "FROM goods_info ,goods_image\n" +
            "WHERE goods_info.goods_id = goods_image.goods_id  \n" +
            "and goods_info.end_time < now()\n" +
            "and goods_info.`status` in  (\"4\",\"5\") \n" +

            "AND user_id = #{userId}\n" +
            "GROUP BY goods_name ")
    List<GoodsInfo> queryAllByUserId2(Long userId);

}